package com.kucun.controller;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.InvocationTargetException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;
import org.sdlc.com.database.BaseDaoInterface;
import org.sdlc.com.database.bean.CglibDynaBean;
import org.sdlc.com.database.impl.MysqlBaseDaoImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.google.gson.JsonArray;
import com.kucun.bean.Billdetail;
import com.kucun.bean.Goods;
import com.kucun.bean.Page;
import com.kucun.bean.Repertory;
import com.kucun.bean.Users;
import com.kucun.service.GoodsService;
import com.kucun.service.RepertoryService;
import com.kucun.util.GsonUtils;
import com.kucun.util.JsonTreeData;
import com.kucun.util.JsonValueProcessorImpl;
import com.kucun.util.TreeNodeService;
import com.kucun.util.TreeNodeUtil;
import com.kucun.util.UID;
import com.times.util.DateUtils;


import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.JsonConfig;


@Controller
@RequestMapping("/report")
public class ReportController {
	private static final Logger logger = Logger.getLogger(ReportController.class);
	/**
	 * 
	 * @param request
	 * @return
	 * @throws IOException 
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 */
	@RequestMapping("/reportalldate.html")
	public String getAllUser(int rows,int page,HttpServletRequest request,HttpServletResponse response) throws IOException, IllegalAccessException, InvocationTargetException{
		  String field=request.getParameter("field");
          String searchertext=request.getParameter("searchertext");
          String searcherSql="";
          if("".equals(searchertext)||searchertext==null){

          }else{
              searchertext = URLDecoder.decode(searchertext, "utf-8");
              searcherSql=" and  "+field+" like CONCAT('%','"+searchertext+"','%' ) ";
          } 
          Page page1=new Page();
          page1.setI(rows*(page-1));
          page1.setRows(rows);
          page1.setFiled(field);
          page1.setSearchertext(searchertext);  
		BaseDaoInterface basedao=MysqlBaseDaoImpl.getInstance();
	        List<CglibDynaBean> lists=null;
	        String sql="SELECT * FROM ( SELECT  t.id, t.parentid, t.clientname, t.clientid, t.assetdescription, t.goodname, t.goodid, t.goodnumber,   t.snno, t.repartoyname, t.repartoyrack, t.repartoyaddress, t.couriernumber, t.createdate,   t.remark, t.beiyon, t.goodsbillno, t.goodsgoout, t.leadingperson, t.leadingtime, t.unit,t.specifications,b.billno,b.billtype,b.states" +
	        		" FROM   billdetail t LEFT JOIN billmaster b ON t.parentid=b.id WHERE 1=1 AND t.states='已完成' AND t.billtype='入库单'  UNION SELECT t.id, t.parentid, t.clientname, t.clientid, t.assetdescription, t.goodname, t.goodid, (t.goodnumber)*-1 AS goodnumber,   t.snno, t.repartoyname, t.repartoyrack, t.repartoyaddress, t.couriernumber, t.createdate,   t.remark, " +
	        		"t.beiyon, t.goodsbillno, t.goodsgoout, t.leadingperson, t.leadingtime, t.unit,t.specifications,t.beiyon AS billno,b.billtype,b.states FROM   billdetail t LEFT JOIN billmaster b ON t.parentid=b.id WHERE 1=1 AND t.states='已完成' AND t.billtype='出库单')s1 WHERE 1=1  "+searcherSql+"  ORDER BY s1.goodname DESC    LIMIT ?,? ";
	        String sql1="SELECT count(goodnumber) as total FROM ( SELECT  t.id, t.parentid, t.clientname, t.clientid, t.assetdescription, t.goodname, t.goodid, t.goodnumber,   t.snno, t.repartoyname, t.repartoyrack, t.repartoyaddress, t.couriernumber, t.createdate,   t.remark, t.beiyon, t.goodsbillno, t.goodsgoout, t.leadingperson, t.leadingtime, t.unit,t.specifications,b.billno,b.billtype,b.states" +
	        		" FROM   billdetail t LEFT JOIN billmaster b ON t.parentid=b.id WHERE 1=1 AND t.states='已完成' AND t.billtype='入库单'  UNION SELECT t.id, t.parentid, t.clientname, t.clientid, t.assetdescription, t.goodname, t.goodid, (t.goodnumber)*-1 AS goodnumber,   t.snno, t.repartoyname, t.repartoyrack, t.repartoyaddress, t.couriernumber, t.createdate,   t.remark, " +
	        		"t.beiyon, t.goodsbillno, t.goodsgoout, t.leadingperson, t.leadingtime, t.unit,t.specifications,b.billno,b.billtype,b.states FROM   billdetail t LEFT JOIN billmaster b ON t.parentid=b.id WHERE 1=1 AND t.states='已完成' AND t.billtype='出库单')s1 WHERE 1=1  "+searcherSql+"   ";
	        logger.info("查询sql明细分页======》"+sql);
	        logger.info("查询sql求和======》"+sql1);
	        lists= basedao.getListCglib(sql,page1.getI(),page1.getRows());
	        List<Billdetail> bilist=new ArrayList<Billdetail>();
	        Page page2=basedao.getEntityBySql(Page.class,sql1);
	        Map<String, Object> jsonMap = new HashMap<String, Object>();//定义map  
            for (CglibDynaBean cg : lists) {
            	Billdetail bi=new Billdetail();
            	//bi.setId((Integer) cg.getValue("id"));
            	bi.setBillno((String) cg.getValue("billno"));
            	bi.setBilltype((String) cg.getValue("billtype"));
            	bi.setClientname((String) cg.getValue("clientname"));
            	bi.setGoodname((String) cg.getValue("goodname"));
            	bi.setGoodnumber((Double) cg.getValue("goodnumber"));
            	bi.setAssetdescription((String) cg.getValue("assetdescription"));
            	bi.setSnno((String) cg.getValue("snno"));
            	bi.setRepartoyname((String) cg.getValue("repartoyname"));
            	bi.setRepartoyrack((String) cg.getValue("repartoyrack"));
            	bi.setRepartoyaddress((String) cg.getValue("repartoyaddress"));
            	bi.setCouriernumber((String) cg.getValue("couriernumber"));
            	bi.setStates((String) cg.getValue("states"));
            	bi.setLeadingperson((String) cg.getValue("leadingperson"));
            	bi.setLeadingtime((Date) cg.getValue("leadingtime"));
            	bi.setGoodsbillno((String) cg.getValue("goodsbillno"));
            	bi.setGoodsgoout((String) cg.getValue("goodsgoout"));
            	bi.setSpecifications((String)cg.getValue("specifications"));
            	//System.out.println((String)cg.getValue("specifications"));
            	bilist.add(bi);
			}
	        jsonMap.put("total",page2.getTotal());//total键 存放总记录数，必须的  
	        jsonMap.put("rows",bilist);//rows键 存放每页记录 list  
	        PrintWriter pw = response.getWriter();
			//date类型的转化方式
		   // Object jsonFromBean=GsonUtils.ConvertJsonDateToDate(jsonMap,"yyyy-MM-dd HH:mm:ss");
		    Object jsonFromBean=GsonUtils.ConvertJsonDateToDate(jsonMap,"yyyy-MM-dd");
		    pw.write(jsonFromBean.toString());        
           	return null;
	}
	/**只统计入库单相关数据
	 * 库存盘点
	 * @param request
	 * @return
	 * @throws IOException 
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 */
	@RequestMapping("/reportstocktaking.html")
	public String getAllStockTaking(int rows,int page,HttpServletRequest request,HttpServletResponse response) throws IOException, IllegalAccessException, InvocationTargetException{
		  String field=request.getParameter("field");
          String searchertext=request.getParameter("searchertext");
		  String field2=request.getParameter("field2");
          String searchertext2=request.getParameter("searchertext2");
          String searcherSql="";
          if("".equals(searchertext)||searchertext==null){

          }else{
              searchertext = URLDecoder.decode(searchertext, "utf-8");
              searcherSql=" and  "+field+" like CONCAT('%','"+searchertext+"','%' ) ";
          } 
          if("".equals(searchertext2)||searchertext2==null){

          }else{
              searchertext2 = URLDecoder.decode(searchertext2, "utf-8");
              searcherSql+=" and  "+field2+" like CONCAT('%','"+searchertext2+"','%' ) ";
          } 
          
          Page page1=new Page();
          page1.setI(rows*(page-1));
          page1.setRows(rows);
          page1.setFiled(field);
          page1.setSearchertext(searchertext);  
		BaseDaoInterface basedao=MysqlBaseDaoImpl.getInstance();
	        List<CglibDynaBean> lists=null;
	        String sql="SELECT * FROM   billdetail t  WHERE 1=1  "+searcherSql+" AND t.states='已完成' AND t.billtype='入库单' AND t.stockrate>0   ORDER BY t.id DESC   LIMIT ?,? ";
	        String sql1="SELECT count(goodnumber) as total FROM billdetail t  WHERE 1=1  "+searcherSql+" AND t.states='已完成' AND t.billtype='入库单'   AND t.stockrate>0  ";
	        logger.info("查询sql明细分页======》"+sql);
	        logger.info("查询sql求和======》"+sql1);
	        lists= basedao.getListCglib(sql,page1.getI(),page1.getRows());
	        List<Billdetail> bilist=new ArrayList<Billdetail>();
	        Page page2=basedao.getEntityBySql(Page.class,sql1);
	        Map<String, Object> jsonMap = new HashMap<String, Object>();//定义map  
            for (CglibDynaBean cg : lists) {
            	Billdetail bi=new Billdetail();
            	//bi.setId((Integer) cg.getValue("id"));
            	bi.setBillno((String) cg.getValue("beiyon"));
            	bi.setBeiyon((String)cg.getValue("beiyon"));
            	bi.setBilltype((String) cg.getValue("billtype"));
            	bi.setClientname((String) cg.getValue("clientname"));
            	bi.setGoodname((String) cg.getValue("goodname"));
            	bi.setGoodnumber((Double) cg.getValue("goodnumber"));
            	bi.setAssetdescription((String) cg.getValue("assetdescription"));
            	bi.setSnno((String) cg.getValue("snno"));
            	bi.setUnit((String) cg.getValue("unit"));
            	bi.setRepartoyname((String) cg.getValue("repartoyname"));
            	bi.setRepartoyrack((String) cg.getValue("repartoyrack"));
            	bi.setRepartoyaddress((String) cg.getValue("repartoyaddress"));
            	bi.setCouriernumber((String) cg.getValue("couriernumber"));
            	bi.setStates((String) cg.getValue("states"));
            	bi.setLeadingperson((String) cg.getValue("leadingperson"));
            	bi.setLeadingtime((Date) cg.getValue("leadingtime"));
            	bi.setGoodsbillno((String) cg.getValue("goodsbillno"));
            	bi.setGoodsgoout((String) cg.getValue("goodsgoout"));
            	bi.setSpecifications((String)cg.getValue("specifications"));
            	bi.setStockrate((Double) cg.getValue("stockrate"));
            	//System.out.println((String)cg.getValue("specifications"));
            	bilist.add(bi);
			}
	        jsonMap.put("total",page2.getTotal());//total键 存放总记录数，必须的  
	        jsonMap.put("rows",bilist);//rows键 存放每页记录 list  
	        PrintWriter pw = response.getWriter();
			//date类型的转化方式
		   // Object jsonFromBean=GsonUtils.ConvertJsonDateToDate(jsonMap,"yyyy-MM-dd HH:mm:ss");
		    Object jsonFromBean=GsonUtils.ConvertJsonDateToDate(jsonMap,"yyyy-MM-dd");
		    pw.write(jsonFromBean.toString());        
           	return null;
	}
	@RequestMapping("/exportdate.html")
	public String toExportDate(HttpServletRequest request,HttpServletResponse response) throws Exception{
		response.reset();  
        request.setCharacterEncoding("UTF-8");  
        String hf=request.getParameter("hfs");  
       // System.out.println(hf);
        String filename=request.getParameter("filename"); 
        String type=request.getParameter("type");  
        String exportname=filename;  
            if(type.equals("excel"))  
            {  
                exportname+=".xls";  
                response.setHeader("Content-disposition", "attachment; filename="+java.net.URLEncoder.encode(exportname, "UTF-8")+"");  
                //response.setContentType("application/msexcel;charset=utf-8");  
                //response.setContentType("application/vnd.ms-excel;charset=utf-8");
                // 设置请求
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
            }  
            else if(type.equals("word"))  
            {  
                exportname+=".doc";  
                response.setHeader("Content-disposition", "attachment; filename="+java.net.URLEncoder.encode(exportname, "UTF-8")+"");  
                response.setContentType("application/ms-word;charset=UTF-8");  
            }  
          
	        PrintWriter out;  
	        out = response.getWriter();  
	        out.println(hf);  
	        out.close();  
       
		return null;
	}
	
}
